﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using System.IO;

namespace Alex.Library.Common
{
    /// <summary>
    /// 基于OpenXml的Office操作Helper类
    /// </summary>
    public class MsOfficeXHelper
    {
        /// <summary>
        /// 写Excel
        /// </summary>
        /// <param name="stream">Excel文件流</param>
        /// <param name="sheetName">Sheet名称</param>
        /// <param name="data">写入的数据集，当集合中包含标题行时将会取第一个并写入到Excel第一行中</param>
        public static void WriteExcel(Stream stream, string sheetName, IEnumerable<ExcelRow> data)
        {
            using (var xlDoc = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {

                WorkbookPart bookPart = xlDoc.AddWorkbookPart();
                Workbook book = new Workbook();
                bookPart.Workbook = book;

                WorksheetPart sheetPart = bookPart.AddNewPart<WorksheetPart>();
                SheetData sd = new SheetData();
                Worksheet worksheet = new Worksheet(sd);
                sheetPart.Worksheet = worksheet;

                Sheets sheets = new Sheets();
                book.AppendChild<Sheets>(sheets);

                Sheet sheet = new Sheet() { Id = bookPart.GetIdOfPart(sheetPart), SheetId = 1001, Name = sheetName };
                sheets.Append(sheet);

                ExcelRow titleRow = data.FirstOrDefault(r => r.IsTitleRow);
                if (titleRow != null)
                {
                    List<Cell> cells = new List<Cell>();
                    foreach (string item in titleRow)
                    {
                        Cell cell = new Cell()
                        {
                            CellValue = new CellValue(item),
                            DataType = new EnumValue<CellValues>(CellValues.String)
                        };
                    }
                    sd.Append(new Row(cells));
                }

                IEnumerable<ExcelRow> dataRows = data.Where(r => !r.IsTitleRow);
                List<Row> rs = new List<Row>();
                var dt = new EnumValue<CellValues>(CellValues.String);
                foreach (ExcelRow dr in dataRows)
                {
                    List<Cell> cs = new List<Cell>();
                    foreach (string item in dr)
                    {
                        cs.Add(new Cell()
                        {
                            CellValue = new CellValue(item),
                            DataType = dt
                        });
                    }
                    rs.Add(new Row(cs));
                }
                sd.Append(rs);
            }
        }
        /// <summary>
        /// 读Excel
        /// </summary>
        /// <param name="stream">Excel文件流</param>
        /// <param name="sheetName">Sheet名称</param>
        /// <param name="offsetRowIndex">起始行索引</param>
        /// <param name="includeTitleRow">是否包含标题行，当参数为true时取offsetRowIndex行作为标题行</param>
        /// <returns></returns>
        public static IEnumerable<ExcelRow> ReadExcel(Stream stream, string sheetName, int offsetRowIndex = 0, bool includeTitleRow = false)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
            {
                Sheet sheet = document.WorkbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == sheetName);
                if (sheet == null)
                {
                    throw new Exception("名为" + sheetName + "的Sheet不存在.");
                }

                WorksheetPart sheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
                SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                IEnumerable<Row> rows = sheetPart.Worksheet.Descendants<Row>();
                List<ExcelRow> result = new List<ExcelRow>();

                int rowcount = 0;                
                int colindex = 0;
                ExcelRow dr;
                foreach (Row row in rows)
                {
                    if (row.RowIndex < offsetRowIndex)
                    {
                        continue;
                    }

                    if (rowcount++ == 0 && includeTitleRow)
                    {
                        dr = new ExcelRow(true);
                    }
                    else
                    {
                        dr = new ExcelRow(false);
                    }
                    
                    colindex = 0;
                    foreach (Cell cell in row)
                    {                       
                        dr[colindex] = String.Empty;

                        if (cell.ChildElements.Count > 0)
                        {
                            dr[colindex] = cell.CellValue.InnerText;
                            if (cell.DataType != null && cell.DataType == CellValues.SharedString)
                            {

                                dr[colindex] = stringTable.ChildElements[int.Parse(dr[colindex])].InnerText;
                            }
                        }
                        colindex++;
                    }

                    result.Add(dr);
                }
                return result;
            }
        }
    }
    /// <summary>
    /// Excel行对象
    /// </summary>
    public class ExcelRow : IEnumerable<string>
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="isTitleRow">是否为标题行</param>
        public ExcelRow(bool isTitleRow)
        {
            IsTitleRow = isTitleRow;
            _list = new List<string>();
        }
        /// <summary>
        /// 获取该行是否为标题
        /// </summary>
        public bool IsTitleRow { get; private set; }

        private List<string> _list;
        /// <summary>
        /// 按索引取值
        /// </summary>
        /// <param name="index">列索引</param>
        /// <returns></returns>
        public string this[int index]
        {
            get
            {
                return _list[index];
            }
            set
            {
                if (index > _list.Count - 1)
                {
                    _list.Add(value);
                }
                else
                {
                    _list[index] = value;
                }
            }
        }
        /// <summary>
        /// 列数
        /// </summary>
        public int Count
        {
            get
            {
                return _list.Count;
            }
        }
        /// <summary>
        /// 添加列
        /// </summary>
        /// <param name="item">列</param>
        public void Add(string item)
        {
            _list.Add(item);
        }
        /// <summary>
        /// 删除列
        /// </summary>
        /// <param name="item">列</param>
        public void Remove(string item)
        {
            _list.Remove(item);
        }
        /// <summary>
        /// 获取列的索引
        /// </summary>
        /// <param name="item">列</param>
        /// <returns></returns>
        public int IndexOf(string item)
        {
            return _list.IndexOf(item);
        }
        /// <summary>
        /// 判断是否包含列
        /// </summary>
        /// <param name="item">列</param>
        /// <returns></returns>
        public bool Contains(string item)
        {
            return _list.Contains(item);
        }
        /// <summary>
        /// 获取迭代器
        /// </summary>
        /// <returns></returns>
        public IEnumerator<string> GetEnumerator()
        {
            return _list.GetEnumerator();
        }
        /// <summary>
        /// 获取迭代器
        /// </summary>
        /// <returns></returns>
        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return _list.GetEnumerator();
        }
    }
}